package com.xm.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mysql.jdbc.StringUtils;
import com.xm.entity.News;
import com.xm.entity.Product;
import com.xm.uti.DBManager;
import com.xm.uti.QueryDataResult;

/**
 * 新闻持久层的实现类
 * 
 * @author tyf
 *
 */
public class NewsDaoImpl {
	
	
	/**
	 * 所有新闻列表
	 * 
	 * @return
	 */
	public ArrayList<News> getAllList(String typeId) {
		String sql = "select * from news where 1=1 ";
		if (typeId != null && !typeId.equals("")) {
			sql = sql + "and typeId = " + typeId;
		}
		sql = sql + " order by date desc ";
		System.out.println(sql);
		QueryDataResult executeQuery = DBManager.executeQuery(sql);
		ArrayList<News> list = new ArrayList<News>();
		if (executeQuery != null) {
			ResultSet rs = executeQuery.rs;
			try {
				while (rs.next()) {
					News e = new News();
					e.setId(rs.getInt("id"));
					e.setTitle(rs.getString("title"));
					e.setContent(rs.getString("content"));
					e.setTypeId(rs.getInt("typeId"));
					e.setDate(rs.getString("date"));
					e.setIsShow(rs.getString("isShow"));
					list.add(e);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				executeQuery.close();
			}
		}
		System.out.println(list);
		return list;
	}
	/**
	 * 查询新闻
	 * @param title
	 * @param newsTypeId
	 * @param mindate
	 * @param maxdate
	 * @param page
	 * @param limit
	 * @return
	 */
	public ArrayList<News> getNewsList(String title,String newsTypeId,String mindate,String maxdate,String page,String limit) {
		String sql = "select n.*,nt.name from news as n left join newstype as nt on n.typeId=nt.id where 1=1 ";
		if(!StringUtils.isNullOrEmpty(title)) {
			sql = sql + " and title like '%"+title+"%'";
		}
		if(!StringUtils.isNullOrEmpty(newsTypeId)) {
			sql = sql + " and typeId = '"+ newsTypeId+"' ";
		}
		if(!StringUtils.isNullOrEmpty(mindate)) {
			sql = sql + " and date >= '"+mindate+"'";
		}
		if(!StringUtils.isNullOrEmpty(maxdate)) {
			sql = sql + " and date <= '"+maxdate+"'";
		}
		sql = sql + " order by date desc";
		if(!StringUtils.isNullOrEmpty(page) && !StringUtils.isNullOrEmpty(limit)) {
			int p = Integer.valueOf(page);
			int c = Integer.valueOf(limit);
			int start = (p-1) * c;
			sql = sql + " limit "+start + ", "+c;
		}
		
		System.out.println(sql);
		QueryDataResult executeQuery = DBManager.executeQuery(sql);
		ArrayList<News> list = new ArrayList<News>();
		if(executeQuery != null) {
			ResultSet rs = executeQuery.rs;
			try {
				while(rs.next()) {
					News e = new News();
					e.setId(rs.getInt("id"));
					e.setTitle(rs.getString("title"));
					e.setContent(rs.getString("content"));
					e.setTypeId(rs.getInt("typeId"));
					e.setDate(rs.getString("date"));
					e.setIsShow(rs.getString("isShow"));
					e.setName(rs.getString("name"));
					list.add(e);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				executeQuery.close();
			}
		}
		System.out.println(list);
		return list;
	}
	/**
	 * 返回6条数据
	 * 
	 * @return
	 */
	public ArrayList<News> getListLimit6() {
		String sql = "select * from news order by date desc limit 6";
		QueryDataResult executeQuery = DBManager.executeQuery(sql);
		ArrayList<News> list = new ArrayList<News>();
		if (executeQuery != null) {
			ResultSet rs = executeQuery.rs;
			try {
				while (rs.next()) {
					News e = new News();
					e.setId(rs.getInt("id"));
					e.setTitle(rs.getString("title"));
					e.setContent(rs.getString("content"));
					e.setTypeId(rs.getInt("typeId"));
					e.setDate(rs.getString("date"));
					e.setIsShow(rs.getString("isShow"));
					list.add(e);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				executeQuery.close();
			}
		}
		System.out.println(list);
		return list;
	}

	/**
	 * 返回4条数据，首页展示
	 * 
	 * @return
	 */
	public ArrayList<News> getListLimit4() {
		String sql = "select * from news where isShow='是' limit 4";
		QueryDataResult executeQuery = DBManager.executeQuery(sql);
		ArrayList<News> list = new ArrayList<News>();
		if (executeQuery != null) {
			ResultSet rs = executeQuery.rs;
			try {
				while (rs.next()) {
					News e = new News();
					e.setId(rs.getInt("id"));
					e.setTitle(rs.getString("title"));
					e.setContent(rs.getString("content"));
					e.setTypeId(rs.getInt("typeId"));
					e.setDate(rs.getString("date"));
					e.setIsShow(rs.getString("isShow"));
					list.add(e);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				executeQuery.close();
			}
		}
		System.out.println(list);
		return list;
	}

	/**
	 * 新闻id查取详情
	 * 
	 * @param nid
	 * @return
	 */
	public News getEmpById(int id) {
		String sql = "select id,title,content,typeId,date,isShow from news where id = " + id;
		QueryDataResult executeQuery = DBManager.executeQuery(sql);
		if (executeQuery != null) {
			ResultSet rs = executeQuery.rs;
			try {
				while (rs.next()) {
					News e = new News();
					e.setId(rs.getInt("id"));
					e.setTitle(rs.getString("title"));
					e.setContent(rs.getString("content"));
					e.setTypeId(rs.getInt("typeId"));
					e.setDate(rs.getString("date"));
					e.setIsShow(rs.getString("isShow"));
					return e;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				executeQuery.close();
			}
		}
		return null;
	}

	/**
	 * 查询上一页
	 * 
	 * @param id
	 * @return
	 */
	public News getEmpByIdLimit1(int id) {
		String sql = "select id,title,content,typeId,date,isShow from news where id = " + id;
		String sql2 = "select * from news where id > id order by date desc limit 1";
		QueryDataResult executeQuery = DBManager.executeQuery(sql);
		if (executeQuery != null) {
			ResultSet rs = executeQuery.rs;
			try {
				while (rs.next()) {
					News e = new News();
					e.setId(rs.getInt("id"));
					e.setTitle(rs.getString("title"));
					e.setContent(rs.getString("content"));
					e.setTypeId(rs.getInt("typeId"));
					e.setDate(rs.getString("date"));
					e.setIsShow(rs.getString("isShow"));
					return e;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				executeQuery.close();
			}
		}
		return null;
	}
	/**
	 * 增
	 */
	 public boolean insertNews(News news) {
		    String sql = "insert into news (title,content,typeId,date,isShow) values('" +news.getTitle() + "','" + news.getContent() + "','"
		        + news.getTypeId() + "','"+ news.getDate()+"','"+ news.getIsShow()+"')";
		    System.out.println("执行的sql语句是" + sql);
		    int i = DBManager.executeUpdate(sql);
		    if(i>0) {
				return true;
			}
			return false;
	 }
		
		/**
		 * 修改
		 * @param Banner
		 * @return
		 */
		public boolean updateNews(News news) {
			String sql = "update news set title = '"+news.getTitle()+ "',content='" + news.getContent() + "',typeId='"
			        + news.getTypeId() + "',date='"+ news.getDate()+"',isShow='"+ news.getIsShow()+"' where id = " + news.getId();
			System.out.println(sql);
			int i = DBManager.executeUpdate(sql);
			if(i>0) {
				return true;
			}
			return false;
		}
		
		/**
		 * 刪除
		 * @param Banner
		 * @return
		 */
		public boolean delNews(int id) {
			String sql = "delete from news where id =" +id;
			System.out.println(sql);
			int i = DBManager.executeUpdate(sql);
			if(i>0) {
				return true;
			}
			return false;
		}

	
}